﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Linq;
using DTO;

namespace DAO
{
    public class VocabularyDAO
    {
        public static List<VOCABULARY> getAllVocabularies()
        {
            List<VOCABULARY> res = null;
            try
            {
                DataClassesMyVocabularyDataContext db = new DataClassesMyVocabularyDataContext();
                res = db.usp_SelectVocabulariesAll().ToList();
            }
            catch (Exception e)
            {
                throw e;
            }
            return res;
        }

        public static List<VOCABULARY> getAllVocabularies(short isTwoWordVerb)
        {
            List<VOCABULARY> res = null;
            try
            {
                DataClassesMyVocabularyDataContext db = new DataClassesMyVocabularyDataContext();
                res = db.usp_SelectVocabulariesAllAndTwoWordVerb(isTwoWordVerb).ToList();
            }
            catch (Exception e)
            {
                throw e;
            }
            return res;
        }

        public static VOCABULARY getVocabulary(String word, String type, int idtopic)
        {
            VOCABULARY res = null;
            try
            {
                DataClassesMyVocabularyDataContext db = new DataClassesMyVocabularyDataContext();
                List<VOCABULARY> resTemp = db.usp_SelectVocabularyByPrimaryKey(word, type, idtopic).ToList();
                if (resTemp.Count > 0)
                {
                    res = resTemp[0];
                }
            }
            catch(Exception e)
            {
                throw e;
            }
            return res;
        }

        public static List<VOCABULARY> getListVocabulariesbyDateToDate(DateTime dateBegin,DateTime dateEnd)
        {
            List<VOCABULARY> res = null;
            try
            {
                DataClassesMyVocabularyDataContext db = new DataClassesMyVocabularyDataContext();
                res = db.usp_SelectVocabulariesByDatetoDate(dateBegin,dateEnd).ToList();
            }
            catch (Exception e)
            {
                throw e;
            }
            return res;
        }

        public static List<VOCABULARY> getListVocabulariesbyDateToDate(DateTime dateBegin, DateTime dateEnd, int idTopic)
        {
            List<VOCABULARY> res = null;
            try
            {
                DataClassesMyVocabularyDataContext db = new DataClassesMyVocabularyDataContext();
                res = db.usp_SelectVocabulariesByDatetoDateAndTopic(dateBegin, dateEnd,idTopic).ToList();
            }
            catch (Exception e)
            {
                throw e;
            }
            return res;
        }

        public static List<VOCABULARY> getListVocabulariesbyDateToDate(DateTime dateBegin, DateTime dateEnd, short isTwoWordVerb)
        {
            List<VOCABULARY> res = null;
            try
            {
                DataClassesMyVocabularyDataContext db = new DataClassesMyVocabularyDataContext();
                res = db.usp_SelectVocabulariesByDatetoDateAndTwoWordVerb(dateBegin, dateEnd, isTwoWordVerb).ToList();
            }
            catch (Exception e)
            {
                throw e;
            }
            return res;
        }

        public static List<VOCABULARY> getListVocabulariesbyDateToDate(DateTime dateBegin, DateTime dateEnd, int idTopic, short isTwoWordVerb)
        {
            List<VOCABULARY> res = null;
            try
            {
                DataClassesMyVocabularyDataContext db = new DataClassesMyVocabularyDataContext();
                res = db.usp_SelectVocabulariesByDatetoDateAndTopicAndTwoWordVerb(dateBegin, dateEnd, idTopic, isTwoWordVerb).ToList();
            }
            catch (Exception e)
            {
                throw e;
            }
            return res;
        }

        public static List<VOCABULARY> getListVocabulariesbyMonth(DateTime date)
        {
            List<VOCABULARY> res = null;
            try
            {
                DataClassesMyVocabularyDataContext db = new DataClassesMyVocabularyDataContext();
                res = db.usp_SelectVocabulariesByMonth(date).ToList();
            }
            catch (Exception e)
            {
                throw e;
            }
            return res;
        }

        public static List<VOCABULARY> getListVocabulariesbyMonth(DateTime date,int idTopic)
        {
            List<VOCABULARY> res = null;
            try
            {
                DataClassesMyVocabularyDataContext db = new DataClassesMyVocabularyDataContext();
                res = db.usp_SelectVocabulariesByMonthAndTopic(date,idTopic).ToList();
            }
            catch (Exception e)
            {
                throw e;
            }
            return res;
        }

        public static List<VOCABULARY> getListVocabulariesbyMonth(DateTime date, short isTwoWordVerb)
        {
            List<VOCABULARY> res = null;
            try
            {
                DataClassesMyVocabularyDataContext db = new DataClassesMyVocabularyDataContext();
                res = db.usp_SelectVocabulariesByMonthAndTwoWordVerb(date, isTwoWordVerb).ToList();
            }
            catch (Exception e)
            {
                throw e;
            }
            return res;
        }

        public static List<VOCABULARY> getListVocabulariesbyMonth(DateTime date, int idTopic, short isTwoWordVerb)
        {
            List<VOCABULARY> res = null;
            try
            {
                DataClassesMyVocabularyDataContext db = new DataClassesMyVocabularyDataContext();
                res = db.usp_SelectVocabulariesByMonthAndTopicAndTwoWordVerb(date, idTopic, isTwoWordVerb).ToList();
            }
            catch (Exception e)
            {
                throw e;
            }
            return res;
        }

        public static List<VOCABULARY>  getListRandVocabularies(int nWord)
        {
            List<VOCABULARY>  res = null;
            try
            {
                DataClassesMyVocabularyDataContext db = new DataClassesMyVocabularyDataContext();
                res = db.usp_SelectVocabulariesRand(nWord).ToList();                
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return res;
        }

        public static List<VOCABULARY> getListRandVocabularies(int nWord,int idTopic)
        {
            List<VOCABULARY> res = null;
            try
            {
                DataClassesMyVocabularyDataContext db = new DataClassesMyVocabularyDataContext();
                res = db.usp_SelectVocabulariesRandInTopic(nWord,idTopic).ToList();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return res;
        }

        public static List<VOCABULARY> getListRandVocabularies(int nWord, short isTwoWordVerb)
        {
            List<VOCABULARY> res = null;
            try
            {
                DataClassesMyVocabularyDataContext db = new DataClassesMyVocabularyDataContext();
                res = db.usp_SelectVocabulariesRandAndTwoWordVerb(nWord, isTwoWordVerb).ToList();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return res;
        }

        public static List<VOCABULARY> getListRandVocabularies(int nWord, int idTopic,short isTwoWordVerb)
        {
            List<VOCABULARY> res = null;
            try
            {
                DataClassesMyVocabularyDataContext db = new DataClassesMyVocabularyDataContext();
                res = db.usp_SelectVocabulariesRandInTopicAndTwoWordVerb(nWord, idTopic, isTwoWordVerb).ToList();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return res;
        }

        public static bool Add(VOCABULARY vc)
        {
            bool res = false;
            try
            {
                DataClassesMyVocabularyDataContext db = new DataClassesMyVocabularyDataContext();
                vc.Phoneticize = ChuanHoaChuoi.ChuanHoaPhienAm(vc.Phoneticize);
                db.VOCABULARies.InsertOnSubmit(vc);
                db.SubmitChanges();
                res = true;
            }
            catch(Exception e)
            {
                throw e;                
            }
            return res;
        }

        public static bool Edit(String word,String type,int idtopic,VOCABULARY vcUpdate)
        {
            bool res = false;
            try
            {
                DataClassesMyVocabularyDataContext db = new DataClassesMyVocabularyDataContext();
                vcUpdate.Phoneticize = ChuanHoaChuoi.ChuanHoaPhienAm(vcUpdate.Phoneticize);
                db.usp_UpdateVocabulary(word, type, idtopic,
                    vcUpdate.Word, vcUpdate.Type, vcUpdate.idTopic, vcUpdate.Phoneticize,
                    vcUpdate.MeanEng, vcUpdate.MeanVi, vcUpdate.Date,vcUpdate.isTwoWordVerb);
                res = true;
            }
            catch (Exception e)
            {
                throw e;
            }
            return res;
        }

        public static bool Remove(VOCABULARY vc)
        {
            bool res = false;
            try
            {
                DataClassesMyVocabularyDataContext db = new DataClassesMyVocabularyDataContext();
                db.usp_DeleteVocabulary(vc.Word, vc.Type, vc.idTopic);                
                res = true;
            }
            catch (Exception e)
            {
                throw e;
            }
            return res;
        }
    }
}
